﻿#region Librerias
using System;
using System.Collections.Generic;
using System.Web;
using System.Data;
#endregion

/// <summary>
/// Class PNatural, dependiente de la clase Person, define una persona natural
/// </summary>
//tratamiento especial para AGE
#region Class

public class clsPNatural : DAC
{
    #region Variables

    private int idPNatural, iCI, iAge, iPhone1, iPhone2, idProfession;
    private String dtBirthDate, sName, sLastName, sAddress, sEmail;
    private const String tabla = "dbo.tblPNatural";

    #endregion

    #region Builder

    /// <summary>
    /// Constructor
    /// </summary>
    public clsPNatural() { }

    #endregion

    #region Methods

    #region Consultations

    public DataSet SeeAllTheRecords() {
        String query = "SELECT row_number() over(order by " + tabla + ".idPNatural) As NUMBER, ";
        query += tabla + ".idPNatural As ID, " + tabla + ".iCI As CI, ";
        query += tabla + ".iAge As AGE, " + tabla + ".iPhone1 As PHONE1, ";
        query += tabla + ".iPhone2 As PHONE2, " + tabla + ".idProfession As PROFESSION, ";
        query += tabla + ".dtBirthDate As BIRTHDATE, " + tabla + ".sName As NAME, ";
        query += tabla + ".sLastName As LASTNAME, " + tabla + ".sAddress As ADDRESS, " + tabla + ".sName+' '+" + tabla +".sLastName As NAMELASTNAME, ";
        query += tabla + ".sEmail As EMAIL ";
        query += "FROM " + tabla;
        return this.ExecuteQuery(query);
    }

    public DataSet SeeAllTheRecords(int startRecord, int maxRecord, String text){
        String query = "SELECT row_number() over(order by " + tabla + ".idPNatural) As NUMBER, ";
        query += tabla + ".idPNatural As ID, " + tabla + ".iCI As CI, ";
        query += tabla + ".iAge As AGE, " + tabla + ".iPhone1 As PHONE1, ";
        query += tabla + ".iPhone2 As PHONE2, " + tabla + ".idProfession As PROFESSION, ";
        query += tabla + ".dtBirthDate As BIRTHDATE, " + tabla + ".sName As NAME, ";
        query += tabla + ".sLastName As LASTNAME, " + tabla + ".sAddress As ADDRESS, ";
        query += tabla + ".sEmail As EMAIL ";
        query += "FROM " + tabla;
        if(!text.Equals("")){
            query += " WHERE " + tabla + ".sName LIKE '%" + text + "%' ";
            query += "OR " + tabla + ".sLastName LIKE '%" + text + "%'";
        }
        if (startRecord == 0 && maxRecord == 0) {
            return this.ExecuteQuery(query);
        }
        else {
            return this.ExecuteQuery(query, startRecord, maxRecord);
        }        
    }

    public DataSet ConsultID(String id) {
        String query = "SELECT ";
        query += tabla + ".idPNatural As ID, " + tabla + ".iCI As CI, ";
        query += tabla + ".iAge As AGE, " + tabla + ".iPhone1 As PHONE1, ";
        query += tabla + ".iPhone2 As PHONE2, " + tabla + ".idProfession As PROFESSION, ";
        query += tabla + ".dtBirthDate As BIRTHDATE, " + tabla + ".sName As NAME, ";
        query += tabla + ".sLastName As LASTNAME, " + tabla + ".sAddress As ADDRESS, ";
        query += tabla + ".sEmail As EMAIL ";
        query += "FROM " + tabla;
        query += " WHERE " + tabla + ".idPNatural =" + id;
        return this.ExecuteQuery(query);
    }

    public DataSet ConsultProfessionID(String id) {
        String query = "SELECT ";
        query += tabla + ".idPNatural As ID, " + tabla + ".iCI As CI, ";
        query += tabla + ".iAge As AGE, " + tabla + ".iPhone1 As PHONE1, ";
        query += tabla + ".iPhone2 As PHONE2, " + tabla + ".idProfession As PROFESSION, ";
        query += tabla + ".dtBirthDate As BIRTHDATE, " + tabla + ".sName As NAME, ";
        query += tabla + ".sLastName As LASTNAME, " + tabla + ".sAddress As ADDRESS, ";
        query += tabla + ".sEmail As EMAIL ";
        query += "FROM " + tabla;
        query += " WHERE " + tabla + ".idProfession =" + id;
        return this.ExecuteQuery(query);
    }

    #endregion

    public int Insert() {
        String query = "INSERT INTO " + tabla;
        query += "(idPNatural, iCI, iAge, iPhone1, iPhone2, idProfession, dtBirthDate, sName, sLastName, sAddress, sEmail) ";
        query += "VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}') SELECT CAST(scope_identity() AS int)";

        String sql = String.Format(query, this.ID, this.ICI, iAge, this.IPhone1, this.IPhone2, this.IdProfession, this.DtBirthDate, this.SName, this.SLastName, this.SAddress, this.SEmail);
        return this.ExecuteDML(sql, "no");
    }

    public int Update(){
        String query = "UPDATE " + tabla + " SET ";
        query += tabla + ".iCI=" + this.ICI + ", " + tabla + ".iAge=" + this.IAge + ", ";
        query += tabla + ".iPhone1=" + this.IPhone1 + ", " + tabla + ".iPhone2=" + this.IPhone2 + ", ";
        query += tabla + ".idProfession=" + this.IdProfession + ", " + tabla + ".dtBirthDate='" + this.DtBirthDate + "', ";
        query += tabla + ".sName='" + this.SName + "', " + tabla + ".sLastName='" + this.SLastName + "', ";
        query += tabla + ".sAddress='" + this.SAddress + "', " + tabla + ".sEmail='" + this.SEmail + "' ";
        query += "WHERE " + tabla + ".idPNatural=" + this.ID;
        return this.ExecuteDML(query, "no");
    }

    public int Delete()
    {
        String query = "DELETE FROM " + tabla + " WHERE " + tabla + ".idPNatural={0}";
        String sql = String.Format(query, this.ID);
        return this.ExecuteDML(sql, "no");
    }

    #endregion

    #region Setters and Getters

    public int IPhone2
    {
        get { return iPhone2; }
        set { iPhone2 = value; }
    }

    public int IPhone1
    {
        get { return iPhone1; }
        set { iPhone1 = value; }
    }

    public int IAge
    {
        get { return iAge; }
        set { iAge = value; }
    }

    public int ICI
    {
        get { return iCI; }
        set { iCI = value; }
    }

    public int ID
    {
        get { return idPNatural; }
        set { idPNatural = value; }
    }

    public String DtBirthDate
    {
        get { return dtBirthDate; }
        set { dtBirthDate = value; }
    }

    public String SAddress
    {
        get { return sAddress; }
        set { sAddress = value; }
    }

    public int IdProfession
    {
        get { return idProfession; }
        set { idProfession = value; }
    }

    public String SName
    {
        get { return sName; }
        set { sName = value; }
    }

    public String SLastName
    {
        get { return sLastName; }
        set { sLastName = value; }
    }

    public String SEmail {
        get { return sEmail; }
        set { sEmail = value; }
    }

    #endregion

}

#endregion